#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.DP0110Job import DP0110Job
from sicost.dp_common_job import DP011MJob__df7
from sicost.config import app_config
from xpy3lib.utils import db_utils as util


class DP0101Job(AbstractDPJob):
    """
    获取消耗信息 按照类型调用不同存储过程

    SU_AJBG_DP0102 主原料
    BPC 能耗
    BPC 锌耗

    从main函数传入参数
    p_account,p_cost_center,p_unit,p_account_period_start,p_account_period_end,p_data_type
    """

    data_type = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        """
        super(DP0101Job, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=p_db_conn_mpp,
                                        p_db_conn_rds=p_db_conn_rds,
                                        p_db_conn_dbprod7=p_db_conn_dbprod7,
                                        p_unit=p_unit,
                                        p_account=p_account,
                                        p_cost_center=p_cost_center,
                                        p_account_period_start=p_account_period_start,
                                        p_account_period_end=p_account_period_end)
        self.data_type = p_data_type
        profile = 'dev'
        config = app_config[profile]
        self.db_conn_mpp2 = util.getConnectionDb2(config.DB_HOST_MPP2_DB2,
                                                  config.DB_PORT_MPP2_DB2,
                                                  config.DB_DBNAME_MPP2_DB2,
                                                  config.DB_USER_MPP2_DB2,
                                                  config.DB_PASSWORD_MPP2_DB2)

    def do_execute(self):
        """
        """
        self.logger.info('DP0101Job.do_execute')

        self.__step_0()

        success, df1 = self.__step_1()
        if success is False:
            return
        #self.generate_excel(p_dataframe=df1, p_file_name="DP0101Job-df1.xls")
        df1.columns = df1.columns.str.upper()
        success, df2 = self.__step_2()
        if success is False:
            return
        #self.generate_excel(p_dataframe=df2, p_file_name="DP0101Job-df2.xls")
        # 将df1 merge df2 left on IN_PRODUCT_CODE 得到df3
        # df3是拼接该机组所对应的主原料科目的dp0102数据
        df2.columns = df2.columns.str.upper()

        df3 = pd.merge(df1, df2, on=['IN_PRODUCT_CODE'], how='left')
        self.logger.info(df3)

        self.__step_2_1(df3=df3)

        #
        success, df4 = self.__step_3()

    def __step_0(self):
        sql = " DELETE FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_DP0103" \
              " WHERE 1=1 " \
              " AND LEFT(PRODUCE_TIME,14) >= '%s'" \
              " AND LEFT(PRODUCE_TIME,14) < '%s'" \
              " AND ACCT = '%s'" \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE = '%s'" \
              " AND DATA_TYPE = '%s'" % (
                  self.account_period_start,
                  self.account_period_end,
                  self.account,
                  self.cost_center,
                  self.unit,
                  self.data_type)
        self.logger.info(sql)
        self.db_conn_rds.execute(sql)

    def __step_1(self):
        # NOTE 子步骤1

        p_app_throw_ai_mode = '%PN%'

        sql = " SELECT " \
              " REC_ID, " \
              " ACCT as ACCOUNT," \
              " DEPARTMENT_CODE AS FACTORY," \
              " UNIT_CODE AS UNIT," \
              " CLASS AS TEAM," \
              " SHIFT," \
              " COST_CENTER," \
              " PRODUCE_TIME AS WORK_TIME," \
              " PRODUCE_START_TIME AS PROCESS_START_TIME," \
              " PRODUCE_END_TIME AS PROCESS_END_TIME," \
              " BYPRODUCT_CODE AS PRODUCT_CODE," \
              " STEELNO AS ST_NO," \
              " INPUT_BYPRODUCT_CODE AS IN_PRODUCT_CODE," \
              " PROD_COILNO as MAT_NO," \
              " ENTRY_COILNO AS IN_MAT_NO," \
              " APPTHROWAIMODE AS APP_THROW_AI_MODE," \
              " ANNEAL_CURVE AS DESIGN_ANNEAL_DIAGRAM_CODE," \
              " TRIM_FLAG," \
              " MAT_ACT_WIDTH," \
              " MAT_ACT_THICK," \
              " ENTRY_MAT_WIDTH AS IN_MAT_WIDTH," \
              " ENTRY_MAT_THICK AS IN_MAT_THICK," \
              " PLAN_NO," \
              " TRIMMING_AMT AS TRIM_WIDTH," \
              " ENTRY_MAT_INDIA AS IN_MAT_INNER_DIA," \
              " CUST_ORDER_NO," \
              " PICKLING_TRIMMING_FLAG AS PICKL_TRIM_FLAG," \
              " SORT_GRADE_CODE," \
              " COATING_TYPE AS LAYER_TYPE," \
              " MAT_ACT_LEN," \
              " MAT_ACT_AREA," \
              " TOP_COATING_WT as TOP_COAT_WT," \
              " BOT_COATING_WT as BOT_COAT_WT," \
              " COALESCE(OUTPUT_WT,0) AS WT," \
              " ACT_OUTPUT_WT AS ACT_WT," \
              " INPUT_WT AS IN_WT," \
              " ACT_INPUT_WT AS ACT_IN_WT," \
              " CASE WHEN APPTHROWAIMODE NOT LIKE '%s' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE COALESCE(ACT_INPUT_WT,0) END AS ACT_N," \
              " INPUT_BYPRODUCT_CODE AS CONSUME_ITEM," \
              " '主原料' AS CONSUME_DESC," \
              " 'T' AS CONSUME_UNIT," \
              " COALESCE(ACT_INPUT_WT,0) AS CONSUME_N," \
              " '%s' AS DATA_TYPE," \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
              " 'BGRAGGCB' AS REC_CREATOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME, "\
              " PRODUCE_NICK_FLAG AS LAS_NOTCH_FLAG" \
              " FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_DP0102" \
              " WHERE 1=1 " \
              " AND ACCT = '%s'" \
              " AND LEFT(PRODUCE_TIME,14) >= '%s'" \
              " AND LEFT(PRODUCE_TIME,14) < '%s'" \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE='%s'" \
              " AND DATA_TYPE = '%s'" % (p_app_throw_ai_mode,
                                         self.data_type,
                                         self.account,
                                         self.account_period_start,
                                         self.account_period_end,
                                         self.cost_center,
                                         self.unit,
                                         self.data_type)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()

        self.logger.info(df)
        success = df.empty is False
        return success, df

    def __step_2(self):
        sql = " SELECT " \
              " WCE," \
              " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE " \
              " FROM " \
              " ACACZG.TACACTY" \
              " WHERE 1=1 " \
              " AND ACCOUNT = '%s'" % (self.account)
        self.logger.info(sql)
        # df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()

        self.logger.info(df)
        success = df.empty is False
        return success, df

    def __step_2_1(self, df3=None):
        # 将df3插入到数据库BGRAGGCB.SU_AJBG_DP0101 中
        df3.drop(['REC_ID'], axis=1, inplace=True)
        df3['REC_CREATOR'] = df3['REC_REVISOR']
        df3['REC_CREATE_TIME'] = df3['REC_REVISOR_TIME']
        df3.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df3.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df3.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df3.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df3.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df3.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df3.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df3.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df3.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df3.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df3.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df3.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df3.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df3.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df3.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df3.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df3.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df3.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        # df3.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df3.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df3.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df3.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df3.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df3.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df3.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df3.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df3.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df3.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df3.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df3.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df3.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df3.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df3.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df3.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df3,
                       p_max_times=5).redo()

    def __step_3(self):
        # sql = " SELECT " \
        #       " DISTINCT WCE " \
        #       " FROM " \
        #       " BGRAGGCB.WH_CF00_XX0660" \
        #       " WHERE 1=1 " \
        #       " AND ACCOUNT= '%s' " \
        #       " AND COST_CENTER='%s' " \
        #       " AND LEFT(WCE,2)<>'15'" \
        #       " AND WCE NOT IN ('59086','59087','59088')" \
        #       " ORDER BY WCE" % (self.account, self.cost_center)
        df7 = DP011MJob__df7(db_conn_dbprod7=self.db_conn_dbprod7,
                             db_conn_rds=self.db_conn_rds,
                             data_type=self.data_type,
                             account=self.account,
                             cost_center_org=self.cost_center,
                             unit=self.unit,
                             account_period_start=self.account_period_start,
                             account_period_end=self.account_period_end)

        # " '%s' AS WCE,"
        # " '%s' AS CONSUME_ITEM,"
        # " '%s' AS CONSUME_DESC,"
        sql = " SELECT " \
              " DISTINCT COST_SUBJECT as WCE " \
              " FROM " \
              " BGTAMAS1.T_ADS_WH_SICB_XX0660" \
              " WHERE 1=1 " \
              " AND ACCT= '%s' " \
              " AND COST_CENTER='%s' " \
              " AND LEFT(COST_SUBJECT,2)<>'15'" \
              " AND COST_SUBJECT NOT IN ('59086','59087','59088')" \
              " ORDER BY COST_SUBJECT" % (self.account, self.cost_center)
        self.logger.info(sql)
        # df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False
        #
        self.logger.info('------------------------->>start')
        for index, row in df.iterrows():
            tmp_wce_list = list()
            # WCE == 成本科目编号
            for i in range(5, 0, -1):
                success, wce_x = self.__select_is_wce_x(p_x=i, p_wce=row['WCE'])
                tmp_wce_list.append(wce_x)
                tmp_wce_history = tmp_wce_list[0:-1]
                # NOTE
                mask = 0
                for tmp_wce in tmp_wce_history:
                    mask = mask | tmp_wce
                if wce_x != 0 and mask == 0:
                    v_wce = self.__create_condition_4_wce(p_x=i, p_wce=row['WCE'])
                    df7['WCE'] = row['WCE']
                    df7['CONSUME_ITEM'] = row['WCE']
                    df7['CONSUME_DESC'] = row['WCE']
                    DP0110Job(p_config=self.config,
                              p_db_conn_mpp=self.db_conn_mpp,
                              p_db_conn_rds=self.db_conn_rds,
                              p_db_conn_dbprod7=self.db_conn_dbprod7,
                              p_unit=self.unit,
                              p_account=self.account,
                              p_cost_center_org=self.cost_center,
                              p_cost_center=self.cost_center,
                              p_account_period_start=self.account_period_start,
                              p_account_period_end=self.account_period_end,
                              p_data_type=self.data_type,
                              p_wce_org=row['WCE'],
                              p_wce=v_wce,
                              p_df7=df7).execute()
                self.logger.info('******************\n')

        self.logger.info('------------------------->>end')

        return success, df

    def __select_is_wce_x(self, p_x=None, p_wce=None):
        # 5 '68300'
        # 4 LEFT('68300',4)||'@＇
        # 3 LEFT('68300',3)||'@@'
        # 2 LEFT('68300',2)||'@@@'
        # 1 LEFT('68300',1)||'@@@@'
        # NOTE LEFT('68300',x)||'5-x个@' ????
        v_wce = self.__create_condition_4_wce(p_x=p_x, p_wce=p_wce)
        sql = " SELECT " \
              " COUNT(1) as IS_WCE%d" \
              " FROM " \
              " BGTARAS1.T_ADS_WH_SICB_DP0110" \
              " WHERE 1=1 " \
              " AND ACCT='%s' " \
              " AND COST_CENTER='%s' " \
              " AND UNIT_CODE='%s' " \
              " AND SOURCE_CODE='%s'" \
              " AND COST_SUBJECT=%s" % (p_x, self.account, self.cost_center, self.unit, self.data_type, v_wce)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False
        key = 'IS_WCE%d' % (p_x)
        v = df.loc[0][key] if success else None
        return success, v

    def __create_condition_4_wce(self, p_x=None, p_wce=None):
        # NOTE LEFT('68300',x)||'5-x个@' ????
        at = (5 - p_x) * ['@']
        v_wce = "LEFT('%s',%d)||'%s'" % (p_wce, p_x, ''.join(at))
        return v_wce
